<?php
class vote_model extends Zmodel{

	function selectEvent($id=null){
            $this->load->database();
            $sql=($id==null)?"SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id ORDER BY V.end_date DESC":"SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.event_id=".$id;
            $event=$this->db->query($sql);
            return $event;
    }
    
    function selectEventbyClosingDate($cat_id,$close_from=null,$close_to=null){
    		$this->load->database();
            if($cat_id==0){
            	if($close_from==null && $close_to==null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id";
            	}elseif($close_from==null && $close_to!=null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date<='".$close_to."'";
            	}elseif ($close_from!=null && $close_to==null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date>='".$close_from."'";
            	}elseif($close_from!=null && $close_to!=null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date BETWEEN '".$close_from."' AND '".$close_to."'";
            	}
            }else{
                if($close_from==null && $close_to==null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE T.vote_item_cat_id=".$cat_id."";
            	}elseif($close_from==null && $close_to!=null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date<='".$close_to."' AND T.vote_item_cat_id=".$cat_id."";
            	}elseif ($close_from!=null && $close_to==null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date>='".$close_from."' AND T.vote_item_cat_id=".$cat_id."";
            	}elseif($close_from!=null && $close_to!=null){
            		$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date BETWEEN '".$close_from."' AND '".$close_to."' AND T.vote_item_cat_id=".$cat_id."";
            	}
            }
            $event=$this->db->query($sql);
            return $event;
    }
	function searchEvent($cat_id,$keyword=null){
            $this->load->database();
            if($cat_id==0){
            	$addSql=($keyword==null)?"":" WHERE T.top_name LIKE '%".strtolower($keyword)."%'";
            	$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id"."".$addSql;
            }else{
            	$addSql=($keyword==null)?"":" AND T.top_name LIKE '%".strtolower($keyword)."%'";
            	$sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id  WHERE T.vote_item_cat_id=".$cat_id."".$addSql;
            }
            $event=$this->db->query($sql);
            return $event;
    }
    
	function selectEventByStatus($status){
            $this->load->database();
            if($status=="active"){
            $sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date>=now()";
            }else if($status=="inactive"){
             $sql="SELECT V.event_id,V.start_date,V.end_date,T.top_id,T.top_name,T.top_desc,T.top_item_max FROM vote_event AS V INNER JOIN vote_topic AS T ON V.top_id=T.top_id WHERE V.end_date<now()";	
            }
            $event=$this->db->query($sql);
            return $event;
    }
    function selectUserEvent($id){
    	    $this->load->database();
            $sql="SELECT U.user_id,U.event_id,U.vote_item_id,U.date_create,E.end_date FROM user_vote_event AS U INNER JOIN vote_event AS E ON U.event_id=E.event_id WHERE U.event_id=".$id ;
            $event=$this->db->query($sql);
            return $event;
    }
    
    function deleteVoteEvent($id=null){
    	   	$this->load->database();
    	   	$Z_USER = Zsession::getUserFromSession();
	    	$userid=(isset($Z_USER["user_id"]))?$Z_USER["user_id"]:0;
    	   	$sql="DELETE FROM user_vote_event WHERE event_id=".$id." AND user_id=".$userid;
			$this->db->query($sql);
    }
    
    function selectCurrentEvent(){
    	    $this->load->database();
    	   	$Z_USER = Zsession::getUserFromSession();
	    	$userid=(isset($Z_USER["user_id"]))?$Z_USER["user_id"]:0;
    	   	$sql="SELECT * FROM user_vote_event WHERE user_id=".$userid;
			return $this->db->query($sql);
    }
   
	function selectItem($top_id,$order=null){
            $this->load->database();
            //Change here
            //$sql="SELECT T.top_id,I.vote_item_id,I.vote_item_desc, I.vote_item_cat_id,C.vote_item_cat_desc FROM (vote_item AS I INNER JOIN vote_topic_item AS T ON T.vote_item_id=I.vote_item_id) INNER JOIN vote_item_category AS C ON C.vote_item_cat_id=I.vote_item_cat_id  WHERE T.top_id=".$top_id ;
			if($order==1){
            	$sql="SELECT T.top_id,C.vote_item_cat_desc,T.vote_item_id,I.vote_item_desc,(SELECT COUNT(V.vote_item_id) FROM user_vote_event AS V WHERE V.vote_item_id=T.vote_item_id) AS vote_count FROM (vote_topic_item AS T INNER JOIN vote_item AS I ON T.vote_item_id=I.vote_item_id) INNER JOIN vote_item_category AS C ON C.vote_item_cat_id=I.vote_item_cat_id WHERE T.top_id=".$top_id." ORDER BY vote_item_desc ASC";
			}else{
				$sql="SELECT T.top_id,C.vote_item_cat_desc,T.vote_item_id,I.vote_item_desc,(SELECT COUNT(V.vote_item_id) FROM user_vote_event AS V WHERE V.vote_item_id=T.vote_item_id) AS vote_count FROM (vote_topic_item AS T INNER JOIN vote_item AS I ON T.vote_item_id=I.vote_item_id) INNER JOIN vote_item_category AS C ON C.vote_item_cat_id=I.vote_item_cat_id WHERE T.top_id=".$top_id." ORDER BY vote_count DESC , vote_item_desc ASC";
			}
            $items=$this->db->query($sql);
            return $items;
    }

    
	function insertVote($event_id,$vote_item_id){
            $this->load->database();
            $Z_USER = Zsession::getUserFromSession();
	    	$user_create=$Z_USER["user_id"];
            $sql="INSERT INTO user_vote_event (user_id,event_id,vote_item_id,date_create) VALUES (".$user_create.",".$event_id.",".$vote_item_id.",NOW())";
            $this->db->query($sql);
    }
    
    function countVote($event_id,$item_id){
    		$this->load->database();
    		$result=0;
    		$sql="SELECT COUNT(event_id) AS event FROM user_vote_event WHERE event_id=".$event_id." AND vote_item_id=".$item_id;
    		$query=$this->db->query($sql);
    		$events=$query->result();
    		$result=(count($events)>0)?$events[0]->event:0;
    		return $result;
    }
    
	function selectCategory($id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT vote_item_cat_id,vote_item_cat_desc FROM vote_item_category":"SELECT vote_item_cat_id,vote_item_cat_desc FROM vote_item_category WHERE vote_item_cat_id=".$id;
			$result=$this->db->query($sql);
			return $result;
		}
}

?>